package com.bj58.data.emailreports.dao;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import com.bj58.data.emailreports.dao.SrcDbPool.DBType;
import com.bj58.groupbuy.util.DateUtil;
import com.bj58.sfft.utility.dao.util.Common;

public class CommonDao {
	/**通用查询类
	 * @param type 
	 * 1：db_94_data_app： 94 data_app
	 * 2：db_131_data_bi： 131 data_app
	 * 3：hive_data_dw： hive data_dw
	 * 4：db_68_bi_sys： 68 bi_sys
	 * 5：db_131_ors： 131（3306）dbwww58com_online_revenue_stats
	 * 
	 * @throws Exception
	 */
	public  <T> List<T>  getCommonStatList(String sql,List<String> cols,Class<T> clazz,int type) throws Exception{
		SrcDbPool db = SrcDbPool.getInstance();
//		System.out.println(sql);
		Connection con = db.getConnection(type);
		if(null == con){
			for(int i = 0 ; i < 2 ; i++){
				System.out.println("try again "+i);
				con = db.getConnection(type);
				if(null != con){
					break;
				}
			}
		}
		Statement st = null;
		ResultSet rs = null;
		try {
			System.out.println("common  con  is  " + con);
			st = con.createStatement();
			rs = st.executeQuery(sql);
			return   populateData(rs,clazz, cols);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return null;
	}
	
	
	/**通用查询-根据VO字段获取所有数据  
	 * @param type 
	 * 1：db_94_data_app： 94 data_app
	 * 2：db_131_data_bi： 131 data_app
	 * 3：hive_data_dw： hive data_dw
	 * 4：db_68_bi_sys： 68 bi_sys
	 * 5：db_131_ors： 131（3306）dbwww58com_online_revenue_stats
	 */
	public  <T> List<T>  getAllColsByVo(String sql,Class<T> clazz,int type) throws Exception{
		SrcDbPool db = SrcDbPool.getInstance();
//		System.out.println(sql);
		Connection con = db.getConnection(type);
		if(null == con){
			for(int i = 0 ; i < 2 ; i++){
				System.out.println("try again "+i);
				con = db.getConnection(type);
				if(null != con){
					break;
				}
			}
		}
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			List<String> cols = new ArrayList<String>();
			Field[]   fs = clazz.getDeclaredFields();
			for(Field f : fs){
				cols.add(f.getName());
			}
			return   populateData(rs,clazz, cols);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return null;
	}
	
	
	
	public  <T> List<T> populateData(ResultSet resultSet, Class<T> clazz,List<String> cols) throws Exception {
		List<T> dataList = new ArrayList<T>();
		
		Method[] mm = clazz.getMethods();
		Map<String,Method> methodMap = new HashMap<String,Method>();
		for(Method m: mm){
			methodMap.put(m.getName(), m);
		}
		
		Field[] fields = clazz.getDeclaredFields();
		while (resultSet.next()) {
			T bean = clazz.newInstance();
			int count = 1;
			for(String col : cols) {
				String methodName =  "set" + col.substring(0,1).toUpperCase()+col.substring(1);
				if(methodMap.containsKey(methodName)) {
					Object columnValueObj = null;
					for(Field field : fields){
						if(field.getName().equals(col)){
							Class<?> filedCls = field.getType();
							if(filedCls == int.class || filedCls == Integer.class) {
								columnValueObj = resultSet.getInt(count);
							} else if(filedCls == String.class) {
								columnValueObj = resultSet.getString(count);
							} else if(filedCls == boolean.class || filedCls == Boolean.class) {
								columnValueObj = resultSet.getBoolean(count);
							} else if(filedCls == byte.class || filedCls == Byte.class) {
								columnValueObj = resultSet.getByte(count);
							} else if(filedCls == short.class || filedCls == Short.class) {
								columnValueObj = resultSet.getShort(count);
							} else if(filedCls == long.class || filedCls == Long.class) {
								columnValueObj = resultSet.getLong(count);
							} else if(filedCls == float.class || filedCls == Float.class) {
								columnValueObj = resultSet.getFloat(count);
							} else if(filedCls == double.class || filedCls == Double.class) {
								columnValueObj = resultSet.getDouble(count);
							} else if(filedCls == BigDecimal.class) {
								columnValueObj = resultSet.getBigDecimal(count);
							} 
							else {
								columnValueObj = resultSet.getObject(count);
							}
							
							if (columnValueObj != null) {
								Method setterMethod = Common.getSetterMethod(clazz, field);
								setterMethod.invoke(bean, new Object[] { columnValueObj });
							}
							count ++;
							break;
						}
					}
				}
			}
			dataList.add(bean);
		}
		return dataList;
	}
	
	
	
	
	/**
	 * @param type 
	 * 1：db_94_data_app： 94 data_app
	 * 2：db_131_data_bi： 131 data_app
	 * 3：hive_data_dw： hive data_dw
	 * 4：db_68_bi_sys： 68 bi_sys
	 * 5：db_131_ors： 131（3306）dbwww58com_online_revenue_stats 
	 */ 
	public   double selectDouble(String sql,int type) throws Exception{
		SrcDbPool db = SrcDbPool.getInstance();
		Connection con = db.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		DecimalFormat  df = new DecimalFormat("0.00");
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				return Double.valueOf(df.format(rs.getDouble(1)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return 0;
	}
	
	/**获取结果 AS  MAP(String:String)
	 * @param type 
	 * 1：db_94_data_app： 94 data_app
	 * 2：db_131_data_bi： 131 data_app
	 * 3：hive_data_dw： hive data_dw
	 * 4：db_68_bi_sys： 68 bi_sys
	 * 5：db_131_ors： 131（3306）dbwww58com_online_revenue_stats
	 */
	public   Map<String,String> selectValueAsMap(String sql,int type) throws Exception{
		SrcDbPool db = SrcDbPool.getInstance();
		Connection con = db.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		Map<String,String> rsMap = new HashMap<String, String>();
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				rsMap.put(String.valueOf(rs.getObject(1)), String.valueOf(rs.getObject(2)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return rsMap;
	}
	
	
	/**LOAD数据
	 * @param filePath  文件路径
	 * @param tableName	表名
	 * @param reg	分隔符
	 * @param type 
	 * 1：db_94_data_app： 94 data_app
	 * 2：db_131_data_bi： 131 data_app
	 * 3：hive_data_dw： hive data_dw
	 * 4：db_68_bi_sys： 68 bi_sys
	 * 5：db_131_ors： 131（3306）dbwww58com_online_revenue_stats
	 * @throws SQLException
	 */
	public boolean loadData(String filePath,String tableName,String reg,int type) throws SQLException{
		SrcDbPool  sp = new SrcDbPool();
		Connection con = sp.getConnection(type);
		Statement st = null;
		StringBuffer sb = new StringBuffer();
		
		sb.append("load data local infile '"+filePath+"'  into  table "+tableName+"  fields terminated by '"+reg+"'  ");
		try {
			st = con.createStatement();
			st.executeQuery(sb.toString());
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			st.close();
			con.close();
		}
		return true;
	}
	
	/**通用查询    将所有数据以字符类型返回，中间以分号隔开
	 * @param Sql
	 * @param colTypes
	 * @param type
	 * @return
	 * @throws Exception
	 */
	public  List<String> searchReturnStrings(String Sql,int type) throws Exception {
		List<String> dataList = new ArrayList<String>();
		SrcDbPool db = SrcDbPool.getInstance();
		Connection con = db.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(Sql);
			ResultSetMetaData   rsmd = rs.getMetaData();
			int numberOfColumns  = rsmd.getColumnCount();
			while(rs.next()){
				StringBuffer result  = new StringBuffer();
				for(int i = 1 ; i <= numberOfColumns ; i++){
					result.append(String.valueOf(rs.getObject(i)));
					if( i < numberOfColumns){
						result.append(";");
					}
				}
				
				dataList.add(result.toString());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return dataList;
	}
	
	
	/**执行Sql
	 * @param type 
	 */
	public  boolean  executeSql(String sql,int type) throws Exception{
		SrcDbPool db = SrcDbPool.getInstance();
		Connection con = db.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			return st.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return false;
	}


	/**通用查询 - 查询所有字段
	 */
	public  <T> List<T>  getAllColsByVoLabel(String sql,Class<T> clazz,int type) throws Exception{
		SrcDbPool db = SrcDbPool.getInstance();
		Connection con = db.getConnection(type);
		if(null == con){
			for(int i = 0 ; i < 2 ; i++){
				System.out.println("try again "+i);
				con = db.getConnection(type);
				if(null != con){
					break;
				}
			}
		}
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			List<String> cols = new ArrayList<String>();
			Field[]   fs = clazz.getDeclaredFields();
			for(Field f : fs){
				cols.add(f.getName());
			}
			return   populateData(rs,clazz, cols);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return null;
	}
	
	
	/** 计算count值
	 */
	public   int  countAll(String sql,int type) throws Exception{
		SrcDbPool db = SrcDbPool.getInstance();
		Connection con = db.getConnection(type);
		if(null == con){
			for(int i = 0 ; i < 2 ; i++){
				System.out.println("try again "+i);
				con = db.getConnection(type);
				if(null != con){
					break;
				}
			}
		}
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int count = rs.getInt(1);
				return count;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return 0;
	}
	
	
	/**执行UPDATE Sql
	 * @param type 
	 */
	public  int  executeUpdate(String sql,int type) throws Exception{
		SrcDbPool db = SrcDbPool.getInstance();
		Connection con = db.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			return st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		
	}
	
	
	/**插入数据
	 * @param <T>
	 * @param cols   插入字段项
	 * @param table 表名
	 * @param clazz model
	 * @param vo  插入对象
	 * @param type 
	 * @return
	 * @throws Exception
	 */
	public  <T>int  insertOneByVo(List<String> cols,String table,Class<T> clazz,T vo,int type) throws Exception{
		SrcDbPool db = SrcDbPool.getInstance();
		Connection con = db.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		try {
			HashMap<String,Field> fieldMap=new HashMap<String,Field>();
			Field[] fs = clazz.getDeclaredFields();
			for (Field field : fs) {
				fieldMap.put(field.getName(),field);
			}
			if(null == cols||cols.isEmpty()){
				cols=new ArrayList<String>();
				for (Field field : fs) {
					if(!field.getName().equals("id"))
						cols.add(field.getName());
				}
			}
			st = con.createStatement();
			StringBuffer columns = new StringBuffer();
			StringBuffer v = new StringBuffer();
			columns.append(" (");
			v.append(" values (");
			Method[] mm = clazz.getMethods();
			Map<String,Method> methodMap = new HashMap<String,Method>();
			for(Method m: mm){
				methodMap.put(m.getName(), m);
			}
			for(int i = 0 ; i<cols.size(); i++){
				String methodName = "get" + cols.get(i).substring(0,1).toUpperCase()+ cols.get(i).substring(1);
				Object value = methodMap.get(methodName).invoke(vo);
				if(value==null&&(i+1) < cols.size()){
						continue;
				}
				else {
					if( fieldMap.get(cols.get(i)).getType()==Date.class)
						value=DateUtil.getStringDate((Date) value, "yyyy-MM-dd HH:mm:ss");
					columns.append(cols.get(i));
					v.append("'"+value+"'");
				}
				if((i+1) < cols.size()){
					columns.append(",");
					v.append(",");
				}else{
					columns.append(")");
					v.append(")");
				}
			}
			StringBuffer sql = new StringBuffer();
			sql.append("insert into "+table);
			sql.append(columns);
			sql.append(v);
			System.out.println(sql);
			return st.executeUpdate(sql.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return 0;
	}
	
	
	/**更新数据
	 * @param <T>
	 * @param table	更新的表
	 * @param condition	更新对象条件
	 * @param clazz	
	 * @param vo
	 * @param type
	 * @return
	 * @throws Exception
	 */
	public  <T>int  updateByVo(String table,String condition ,Class<T> clazz,T vo,int type) throws Exception{
		SrcDbPool db = SrcDbPool.getInstance();
		Connection con = db.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			Map<String,Object> updateValue = new HashMap<String, Object>();
			Field[] fds = clazz.getDeclaredFields();
			Method[] mm = clazz.getMethods();
			Map<String,Method> methodMap = new HashMap<String,Method>();
			for(Method m: mm){
				methodMap.put(m.getName(), m);
			}
			for(Field fd : fds){
				String fName = fd.getName();
				String methodName = "get" + fName.substring(0,1).toUpperCase()+ fName.substring(1);
				Object value = methodMap.get(methodName).invoke(vo);
				updateValue.put(fName, value);
			}
			
			
			StringBuffer sql = new StringBuffer();
			sql.append("update "+table+" set ");
			
			for(Iterator<String> it  = updateValue.keySet().iterator();  it.hasNext();){
				String key = it.next();
				Object value = updateValue.get(key);
				if(null == value){
					continue;
				}
				sql.append(key +"=\""+value+"\"");
				if(it.hasNext()){
					sql.append(",");
				}
			}
			if(!"".equals(condition) && null != condition){
				sql.append(" where "+condition);
			}
			return st.executeUpdate(sql.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return 0;
	}
	
	
	
	
	/**删除数据
	 * @param tableName	表名
	 * @param condition	删除条件
	 * @param type 
	 * @throws SQLException
	 */
	public boolean deleteData(String tableName,String condition,int type) throws SQLException{
		SrcDbPool  sp = new SrcDbPool();
		Connection con = sp.getConnection(type);
		Statement st = null;
		StringBuffer sb = new StringBuffer();
		sb.append("delete from "+tableName+" where 1=1 "+condition);
		try {
			st = con.createStatement();
			st.execute(sb.toString());
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			st.close();
			con.close();
		}
		return false;
	}
	
	
	
	/**通用查询    将所有数据以字符类型返回，中间以分号隔开
	 * @param Sql
	 * @param colTypes
	 * @param type
	 * @return
	 * @throws Exception
	 */
	public  List<String> searchReturnStrings(String Sql,DBType type) throws Exception {
		List<String> dataList = new ArrayList<String>();
		SrcDbPool db = SrcDbPool.getInstance();
		Connection con = db.getConnection(type.getValue());
		System.out.println(con);
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(Sql);
			ResultSetMetaData   rsmd = rs.getMetaData();
			int numberOfColumns  = rsmd.getColumnCount();
			while(rs.next()){
				StringBuffer result  = new StringBuffer();
				for(int i = 1 ; i <= numberOfColumns ; i++){
					result.append(String.valueOf(rs.getObject(i)));
					if( i < numberOfColumns){
						result.append(";");
					}
				}
				
				dataList.add(result.toString());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return dataList;
	}
	
	
	
	/**作为事务，执行一组Sql
	 * @param type 
	 */
	public  boolean  executeSql(List<String> sqlList,int type) throws Exception{
		SrcDbPool db = SrcDbPool.getInstance();
		Connection con = db.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			for(String sql:sqlList){
				st.execute(sql);
			}
			con.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;
		}finally {
			SrcDbPool.close(rs, st, con);
		}
		return true;
	}
		
	
	/**批量插入数据 （事务）
	 * @param table	插入的表名
	 * @param clazz
	 * @param voList	要插入数据集
	 * @param dbType	数据库连接
	 */
	public  <T>boolean  insertVoList(String table,Class<T> clazz,List<T> voList ,int dbType) throws Exception{
		SrcDbPool db = SrcDbPool.getInstance();
		Connection con = db.getConnection(dbType);
		Statement st = null;
		ResultSet rs = null;
		try {
			HashMap<String,Field> fieldMap=new HashMap<String,Field>();
			Field[] fs = clazz.getDeclaredFields();
			for (Field field : fs) {
				fieldMap.put(field.getName(),field);
			}
			
			List<String> cols=new ArrayList<String>();
			for (Field field : fs) {
				if(!field.getName().equals("id"))
					cols.add(field.getName());
			}
			
			st = con.createStatement();
			con.setAutoCommit(false);  //自动提交设置
			
			for(T vo : voList){
				StringBuffer columns = new StringBuffer();
				StringBuffer v = new StringBuffer();
				columns.append(" (");
				v.append(" values (");
				Method[] mm = clazz.getMethods();
				Map<String,Method> methodMap = new HashMap<String,Method>();
				for(Method m: mm){
					methodMap.put(m.getName(), m);
				}
				
				for(int i = 0 ; i<cols.size(); i++){
					String methodName = "get" + cols.get(i).substring(0,1).toUpperCase()+ cols.get(i).substring(1);
					Object value = methodMap.get(methodName).invoke(vo);
					if(value==null&&(i+1) < cols.size()){
							continue;
					}
					else {
						if( fieldMap.get(cols.get(i)).getType()==Date.class)
							value=DateUtil.getStringDate((Date) value, "yyyy-MM-dd HH:mm:ss");
						columns.append(cols.get(i));
						v.append("'"+value+"'");
					}
					if((i+1) < cols.size()){
						columns.append(",");
						v.append(",");
					}else{
						columns.append(")");
						v.append(")");
					}
				}
				StringBuffer sql = new StringBuffer();
				sql.append("insert into "+table);
				sql.append(columns);
				sql.append(v);
				st.executeUpdate(sql.toString());
			}
		
			con.commit();   
			
		} catch (SQLException e) {
			con.rollback(); 
			e.printStackTrace();
			return false;
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return true;
	}
	
	
	
	/**通用查询    每行数据组织成字符串返回    
	 * @param Sql
	 * @param type
	 * @return
	 * @throws Exception
	 */
	public  List<Map<String,Object>> searchDataForIE(String sql,List<String> nameList,int type) throws Exception {
		List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();
		SrcDbPool db = SrcDbPool.getInstance();
//		System.out.println(sql);
		Connection con = db.getConnection(type);
		if(null == con){
			for(int i = 0 ; i < 2 ; i++){
				System.out.println("try again "+i);
				con = db.getConnection(type);
				if(null != con){
					break;
				}
			}
		}
		Statement st = null;
		ResultSet rs = null;
		try {
			System.out.println("ir  con  is  " + con);
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				Map<String,Object> rsMap = new HashMap<String, Object>();
				
				for(int i = 0; i< nameList.size() ; i++){
					Object  value = rs.getObject(i+1);
					if(null == value || "".equals(value) || "null".equals(value)){
						value = "--";
					}
					rsMap.put(nameList.get(i), value);
				}
				dataList.add(rsMap);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			SrcDbPool.close(rs, st, con);
		}
		return dataList;
	}
	
	/**
	 * 根据表名和条件检查是否有数据
	 * @param tableName
	 * @param condition
	 * @param dbID
	 * @return
	 * @throws Exception
	 */
	public boolean checkTable(String tableName,String condition,int dbID)throws Exception{
		String sql2 = "select count(1) from " + tableName;
		if(!StringUtils.isBlank(condition))
			sql2 += " where "+condition;
		int count = countAll(sql2, dbID);
		if(count>0)
			return true;
		return false;
	}
}
